package com.biz.utils;

import java.io.File;  
import java.io.FileInputStream;  
import java.io.IOException;  
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
  
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;



public class ExcelHandler {

	//Excel 2003 
    private final static String XLS = "xls";  
    //Excel 2007
    private final static String XLSX = "xlsx";  
    //分隔符 
    private final static String SEPARATOR = "|";  
  
    
    /** 
     * 由Excel文件的Sheet导出至List 
     * @param file 
     * @param sheetNum 
     * @return 
     */  
    public static List<String> exportListFromExcel(File file, int sheetNum)  throws IOException {  
        return exportListFromExcel(new FileInputStream(file),FilenameUtils.getExtension(file.getName()), sheetNum);  
    }  
  
    
    /** 
     * 由Excel流的Sheet导出至List 
     * @param is 
     * @param extensionName 
     * @param sheetNum 
     * @return 
     * @throws IOException 
     */  
    public static List<String> exportListFromExcel(InputStream is,String extensionName, int sheetNum) throws IOException {  
        Workbook workbook = null;  
        if (extensionName.toLowerCase().equals(XLS)) {  
            workbook = new HSSFWorkbook(is);  
        } else if (extensionName.toLowerCase().equals(XLSX)) {  
            workbook = new XSSFWorkbook(is);  
        }  
        return exportListFromExcel(workbook, sheetNum);  
    }  
  
    
    /** 
     * 由指定的Sheet导出至List 
     * @param workbook 
     * @param sheetNum 
     * @return 
     * @throws IOException 
     */  
    private static List<String> exportListFromExcel(Workbook workbook,int sheetNum) {  
        Sheet sheet = workbook.getSheetAt(sheetNum);  
        // 解析公式结果  
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();  
  
        List<String> list = new ArrayList<String>();  
  
        int minRowIx = sheet.getFirstRowNum();
        int maxRowIx = sheet.getLastRowNum();
        //行循环
        for (int rowIx = minRowIx+1; rowIx <= maxRowIx; rowIx++) 
        {
            Row row = sheet.getRow(rowIx);  
            StringBuilder sb = new StringBuilder();  
            if(row!=null)
            {
            	 //excel列循环
                 for (short colIx = 0; colIx <= 25; colIx++) 
                 {
                     Cell cell = row.getCell(new Integer(colIx));  
                     CellValue cellValue = evaluator.evaluate(cell);  
                     if(colIx==0)
                     {
                    	 if (cellValue == null)
                    	 {
                    		 break;
                    	 }
                    	 else
                    	 {
                    		 if(cellValue.getStringValue()==null)
                    		 {
                    			 sb.append(cellValue.getNumberValue());
                    		 }
                    		 else
                    		 {
                    			 sb.append(cellValue.getStringValue());
                    		 }
                    	 }
                     }
                     else
                     {
                    	 if (cellValue == null) 
                         {
                        	 sb.append("#" + "null");
                         }
                         else
                         {
                        	 if(cellValue.getStringValue()==null)
                    		 {
                                 if (HSSFDateUtil.isCellDateFormatted(cell)){
                                     sb.append("#" + getDateCell(cell));
                                 }else{
                                     DecimalFormat df = new DecimalFormat("#.##");
                                     sb.append("#" + df.format(cellValue.getNumberValue()));
                                 }
                    		 }
                    		 else
                    		 {
                    			 sb.append("#" + cellValue.getStringValue());
                    		 }
                         }
                     }
                    
                    
                 }
            }
            else
            {
            	continue;
            }
            if(!sb.toString().equals(""))
            {
            	list.add(sb.toString());  
            }
            
        }  
        return list;  
    }

    private static String getDateCell(Cell cell){
        SimpleDateFormat sdf = null;
        if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
            sdf = new SimpleDateFormat("HH:mm");
        } else {// 日期
            sdf = new SimpleDateFormat("yyyy-MM-dd");
        }
        Date date = cell.getDateCellValue();
        return sdf.format(date);
    }
}
